package ca.cbc.giscommon.dao;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Formatter;
import java.util.List;

import com.vividsolutions.jts.geom.Geometry;

import ca.cbc.giscommon.entity.Event;
import ca.cbc.giscommon.entity.Source;
import ca.cbc.giscommon.entity.Zone;
import ca.cbc.giscommon.util.GeometryUtil;
import ca.cbc.giscommon.util.StringUtil;

public class EventDAO extends EntityHome<Event>{
	@Override
	public String getFullClassName() {
		return Event.class.getCanonicalName();
	}
	
	public void delete(Source source) {
		getCurrentSession().createQuery("DELETE Event e WHERE e.source = :source")
		.setEntity("source", source)
		.executeUpdate();
	}
	
    public List<Event> listWithZone() {
    	return this.listWithZone(null, null, null, null, -1l, -1l, "pubdate", false, 50, 0);
    }
    
    @SuppressWarnings("unchecked")
	public List<Event> listWithZone(String zoneIds, Geometry bbox, String categoryIds, String eventSubcategoryIds, long startTime, long endTime, String orderBy, boolean isAscending, int limit, int offset) {
    	StringBuilder queryBuilder = null;
    	
    	String withTypeId = "";
    	if(!StringUtil.instance.isNullOrEmpty(eventSubcategoryIds)) {
    		withTypeId = " INNER JOIN event_event_subcategory eet ON s.id = eet.event_id INNER JOIN event_subcategory et on eet.event_subcategory_id = et.id";
    	}
    	
    	queryBuilder = new StringBuilder(new Formatter().format(
    			"SELECT DISTINCT {s.*}, {z.*} " +
    			"FROM zone z, event s " +
    			"LEFT OUTER JOIN source so on s.source_id = so.id " +
    			"LEFT OUTER JOIN category c ON c.id = so.category_id%s " +
    			"WHERE ST_Intersects(z.the_geom, s.the_geom)", withTypeId).toString());
    	
    	
    	if(!StringUtil.instance.isNullOrEmpty(categoryIds)) {
    		queryBuilder.append(" and c.id in (").append(categoryIds).append(")");
    	}
    	
    	if(!StringUtil.instance.isNullOrEmpty(eventSubcategoryIds)) {
    		queryBuilder.append(" and et.id in (").append(eventSubcategoryIds).append(")");
    	}
    	
    	if(!StringUtil.instance.isNullOrEmpty(zoneIds)) {
    		queryBuilder.append(" and z.id in (").append(zoneIds).append(")");
    	} else if (bbox != null) {
    		queryBuilder.append(new Formatter().format(" and ST_Intersects(ST_GeomFromText('%s', %d), s.the_geom)", bbox.toString(), GeometryUtil.instance.getSRID()).toString());
    	}    	
    	
    	if(startTime > 0l && endTime <= 0l) {
    		queryBuilder.append(" and start_time >= '").append(new Timestamp(startTime)).append("'");
    	}
    	
    	if(endTime > 0l && startTime <= 0l) {
    		queryBuilder.append(" and end_time <= '").append(new Timestamp(endTime)).append("'");
    	}	
    	
    	if(startTime > 0l && endTime > 0l && endTime > startTime) {
    		Timestamp startTimeStamp = new Timestamp(startTime);
    		Timestamp endTimeStamp = new Timestamp(endTime);
    		queryBuilder.append(new Formatter().format(" and ((start_time >= '%s' and start_time <= '%s') or (end_time >= '%s' and end_time <= '%s') or (start_time < '%s' and end_time > '%s'))", startTimeStamp, endTimeStamp, startTimeStamp, endTimeStamp, startTimeStamp, endTimeStamp).toString());
    	}
    	
    	queryBuilder.append(new Formatter().format(" ORDER BY s.%s %s", orderBy, getAscOrDesc(isAscending)));
    	
    	if(limit > 0) {
    		queryBuilder.append(" LIMIT " + limit);
    	}
    	
    	if(offset > 0) {
    		queryBuilder.append(" OFFSET " + offset);
    	}
    	
    	List<Object> storyAndZones = getCurrentSession().createSQLQuery(queryBuilder.toString()).addEntity("s", Event.class).addEntity("z", Zone.class).list();
        List<Event> events = new ArrayList<Event>();
        for (Object obj : storyAndZones) {
            Object[] objArray = (Object[]) obj;
            if (objArray.length != 2) {
                logger.error("Error! Something is wrong when fetch events {}.", Arrays.toString(objArray));
                continue;
            }

            Event event = (Event) objArray[0];
            Zone zone = (Zone) objArray[1];
            if(events.contains(event)) {
            	event = events.get(events.indexOf(event));
            	event.addZone(zone);
            } else {
	            event.addZone(zone);
	            events.add(event);
            }
        }
        return events;
    }
}
